﻿using Dapper;
using Dapper.Contrib.Extensions;
using Gx.Common.Core;
using Gx.Model.Entity;
using Gx.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Gx.Dal.Extensions;
using Gx.Dal.Shared;
using Gx.Model.Enums;
using System.Diagnostics.Contracts;
namespace Gx.Dal.Manage
{
    public class LKWarehouseEntryDal : BaseDal<LKWarehouseEntry>
    {

        /// <summary>
        /// 名称是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool AnyName(string name)
        {
            using (var conn = new DbContext())
            {
                var total = conn.Connection.QueryFirst<int>("SELECT COUNT(1) FROM LK_WarehouseEntry WITH(NOLOCK) WHERE IsDeleted=0 AND Name=@name", new { name });
                return total > 0;
            }
        }

        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxLKWarehouseEntryCode()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.QueryFirst<string>(@"SELECT MAX(CAST(SUBSTRING(BillNo, 2,LEN(BillNo)-1) AS int)) AS MaxBillNumber
FROM LK_WarehouseEntry WITH(NOLOCK)
WHERE ISNUMERIC(SUBSTRING(BillNo, 2, LEN(BillNo) - 1)) = 1");
            }
        }

        //获取商品规格最大编号
        public string GetMaxLKWarehouseEntrySpecCode()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.QueryFirst<string>(@"SELECT MAX(CAST(SUBSTRING(GoodsCode, 2,LEN(GoodsCode)) AS int)) AS MaxGoodsNoNumber
FROM LK_GoodsCodes WITH(NOLOCK)
WHERE BillType=1 AND ISNUMERIC(SUBSTRING(GoodsCode, 2,LEN(GoodsCode))) = 1");
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PagedList<LKWarehouseEntry> GetLKWarehouseEntryPageList(LKWarehouseEntryInput input)
        {

            var ids = GetEntryIdList(input);


            string sql = "SELECT * FROM LK_WarehouseEntry WITH(NOLOCK) WHERE IsDeleted=0 AND RentalWarehouseType=@RentalWarehouseType @WHERE";
            List<string> conditions = new List<string>();
            //判断input的属性是否为空，如果不为空则添加到conditions中

            if (ids != null)
            {
                if (ids.Count == 0)
                {
                    ids.Add(0);
                }
                input.Ids = ids;
                conditions.Add("Id IN @Ids");
            }

            if (!string.IsNullOrWhiteSpace(input.BillNo))
            {
                input.BillNo = $"%{input.BillNo}%";
                conditions.Add("BillNo LIKE @BillNo");
            }
            if (input.CustomerId.HasValue)
            {
                conditions.Add("CustomerId=@CustomerId");
            }
            if (input.BeginTime.HasValue)
            {
                conditions.Add("CreateTime>=@BeginTime");
            }
            if (input.EndTime.HasValue)
            {
                conditions.Add("CreateTime<=@EndTime");
            }


            if (!string.IsNullOrWhiteSpace(input.ContainerNumber))
            {
                input.ContainerNumber = $"%{input.ContainerNumber}%";
                conditions.Add("ContainerNumber LIKE @ContainerNumber");
            }
            if (input.IsReview.HasValue)
            {
                conditions.Add("IsReview=@IsReview");
            }
            if (input.ModifyUserId.HasValue)
            {
                conditions.Add("ModifyUserId=@ModifyUserId");
            }
            if (input.ModifyBeginTime.HasValue)
            {
                conditions.Add("ModifyTime>=@ModifyBeginTime");
            }
            if (input.ModifyEndTime.HasValue)
            {
                conditions.Add("ModifyTime<=@ModifyEndTime");
            }

            if (UserContext.RoleId != (int)Roles.管理员)
            {
                conditions.Add($"CompanyId={UserContext.CompanyId}");
            }

            string condition = string.Empty;
            if (conditions.Any())
            {
                condition = $" AND {string.Join(" AND ", conditions)}";
            }
            sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

            using (var conn = new DbContext())
            {
                return conn.Connection.GetPagedList<LKWarehouseEntry>(sql, "CreateTime Desc", input);
            }
        }

        /// <summary>
        /// 添加商品入库单
        /// </summary>
        /// <param name="lKWarehouseEntryDto"></param>
        /// <returns></returns>
        public bool AddLKWarehouseEntry(LKWarehouseEntryDto lKWarehouseEntryDto)
        {
            return UseTransaction((conn, trans) =>
            {
                var entry = lKWarehouseEntryDto.WarehouseEntry;
                var result = conn.Insert(entry, trans);
                if (result == 0) return false;
                var details = lKWarehouseEntryDto.Details;
                if (details != null && details.Any())
                {
                    var adds = details.Where(c => c.Id == 0).ToList();
                    var updates = details.Where(c => c.Id > 0).ToList();


                    if (updates.Any())
                    {
                        conn.Update(updates, trans);

                        var upids = updates.Select(s => s.Id);
                        var deletes = conn.GetListByWhere<LKWarehouseEntryDetail>("EntryId=@entryId AND Id Not IN @ids", new { entryId = entry.Id, ids = upids }, trans).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKWarehouseEntryDetail, int>(deletes.ToList(), trans);
                        }
                    }
                    if (adds.Any())
                    {
                        adds.ForEach(item =>
                        {
                            item.EntryId = entry.Id;
                            item.CreateTime = DateTime.Now;
                        });
                        conn.Insert(adds, trans);
                    }

                }

                var costs = lKWarehouseEntryDto.Costs;
                if (costs != null && costs.Any())
                {
                    var adds = costs.Where(c => c.Id == 0).ToList();
                    var updates = costs.Where(c => c.Id > 0).ToList();

                    if (updates.Any())
                    {
                        conn.Update(updates);

                        var upids = updates.Select(s => s.Id);
                        var deletes = conn.GetListByWhere<LKWarehouseEntryCost>("EntryId=@entryId AND Id Not IN @ids", new { entryId = entry.Id, ids = upids }, trans).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKWarehouseEntryCost, int>(deletes.ToList(), trans);
                        }
                    }

                    if (adds.Any())
                    {
                        adds.ForEach(item =>
                        {
                            item.EntryId = entry.Id;
                            item.CreateTime = DateTime.Now;
                        });
                        conn.Insert(adds, trans);
                    }
                }
                
                //更新合同状态
                if (entry.Status == (int)EntryBillStatus.Confirmed)
                {
                    if (entry.RentalWarehouseType == (int)BillType.ZeroWarehouseContract)
                    {
                        conn.Execute("UPDATE LK_ZeroWarehouseContract SET Status=1 WHERE ContractNo=@contractNo", new { contractNo = entry.ContractNo }, trans);
                    }
                    else
                    {
                        conn.Execute("UPDATE LK_WarehouseContract SET Status=1 WHERE ContractNo=@contractNo", new { contractNo = entry.ContractNo }, trans);
                    }
                }
                return result > 0;
            });
        }

        /// <summary>
        /// 编辑商品入库单
        /// </summary>
        /// <param name="lKWarehouseEntryDto"></param>
        /// <returns></returns>
        public bool EditLKWarehouseEntry(LKWarehouseEntryDto lKWarehouseEntryDto)
        {
            return UseTransaction((conn, trans) =>
            {
                var lKWarehouseEntry = lKWarehouseEntryDto.WarehouseEntry;
                var result = conn.Update(lKWarehouseEntry, trans);

                var details = lKWarehouseEntryDto.Details;
                if (details != null && details.Any())
                {
                    var adds = details.Where(c => c.Id == 0).ToList();
                    var updates = details.Where(c => c.Id > 0).ToList();


                    if (updates.Any())
                    {
                        conn.Update(updates, trans);

                        var upids = updates.Select(s => s.Id);
                        var deletes = conn.GetListByWhere<LKWarehouseEntryDetail>("EntryId=@entryId AND Id Not IN @ids", new { entryId = lKWarehouseEntry.Id, ids = upids }, trans).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKWarehouseEntryDetail, int>(deletes.ToList(), trans);
                        }
                    }
                    if (adds.Any())
                    {
                        adds.ForEach(item =>
                        {
                            item.EntryId = lKWarehouseEntry.Id;
                            item.CreateTime = DateTime.Now;
                        });
                        conn.Insert(adds, trans);
                    }

                }
                else
                {
                    conn.Execute("UPDATE LK_WarehouseEntryDetail SET IsDeleted=1 WHERE EntryId=@entryId", new { entryId = lKWarehouseEntry.Id }, trans);
                }

                var costs = lKWarehouseEntryDto.Costs;
                if (costs != null && costs.Any())
                {
                    var adds = costs.Where(c => c.Id == 0).ToList();
                    var updates = costs.Where(c => c.Id > 0).ToList();

                    if (updates.Any())
                    {
                        conn.Update(updates, trans);

                        var upids = updates.Select(s => s.Id);
                        var deletes = conn.GetListByWhere<LKWarehouseEntryCost>("EntryId=@entryId AND Id Not IN @ids", new { entryId = lKWarehouseEntry.Id, ids = upids }, trans).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKWarehouseEntryCost, int>(deletes.ToList(), trans);
                        }
                    }

                    if (adds.Any())
                    {
                        adds.ForEach(item =>
                        {
                            item.EntryId = lKWarehouseEntry.Id;
                            item.CreateTime = DateTime.Now;
                        });
                        conn.Insert(adds, trans);
                    }
                }
                else
                {
                    conn.Execute("UPDATE LK_WarehouseEntryCost SET IsDeleted=1 WHERE EntryId=@entryId", new { entryId = lKWarehouseEntry.Id }, trans);
                }

                //更新合同状态
                if (lKWarehouseEntry.Status == (int)EntryBillStatus.Confirmed)
                {
                    if (lKWarehouseEntry.RentalWarehouseType == (int)BillType.ZeroWarehouseContract)
                    {
                        conn.Execute("UPDATE LK_ZeroWarehouseContract SET Status=1 WHERE ContractNo=@contractNo", new { contractNo = lKWarehouseEntry.ContractNo }, trans);
                    }
                    else
                    {
                        conn.Execute("UPDATE LK_WarehouseContract SET Status=1 WHERE ContractNo=@contractNo", new { contractNo = lKWarehouseEntry.ContractNo }, trans);
                    }
                }

                return result;
            });
        }

        /// <summary>
        /// 删除商品入库单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteLKWarehouseEntry(int id)
        {
            return UseTransaction((conn, trans) =>
            {
                var result = conn.Execute("UPDATE LK_WarehouseEntry SET IsDeleted=1 WHERE Id=@id", new { id }, trans);
                conn.Execute("UPDATE LK_WarehouseEntryDetail SET IsDeleted=1 WHERE EntryId=@id", new { id }, trans);
                conn.Execute("UPDATE LK_WarehouseEntryCost SET IsDeleted=1 WHERE EntryId=@id", new { id }, trans);
                return result > 0;
            });
        }
        private List<int> GetEntryIdList(LKWarehouseEntryInput input)
        {


            using (var conn = new DbContext())
            {
                List<int> houseids = null;//new List<int>();
                if (input.WarehouseId.HasValue && input.WarehouseId != 0)
                {
                    houseids = new List<int>
                    {
                        input.WarehouseId.Value
                    };
                    string housesql = @"WITH RecursiveHouse AS (
    SELECT Id, ParentId
    FROM LK_WareHouse WITH(NOLOCK)
    WHERE ParentId = @WarehouseId  -- 这里的@ParentID是要查询子节点的起始节点ID
    UNION ALL
    SELECT o.Id, o.ParentId
    FROM LK_WareHouse AS o WITH(NOLOCK)
    INNER JOIN RecursiveHouse AS r ON o.ParentId = r.Id
)
SELECT Id FROM RecursiveHouse;";

                    var hids = conn.Connection.Query<int>(housesql, new { input.WarehouseId }).ToList();

                    if (hids != null && hids.Any())
                    {
                        houseids.AddRange(hids);
                    }
                }

                string sql = @"SELECT d.EntryId FROM dbo.LK_WarehouseEntryDetail d WITH(NOLOCK)
LEFT JOIN dbo.LK_Goods g WITH(NOLOCK) ON d.GoodsId=g.Id AND g.IsDeleted=0
LEFT JOIN dbo.LK_GoodsSpec gs WITH(NOLOCK) ON d.GoodsSpecId=gs.Id AND gs.IsDeleted=0
WHERE d.IsDeleted=0  @WHERE";

                List<string> conditions = new List<string>();
                if (houseids != null)
                {
                    input.HouseIds = houseids;
                    conditions.Add("d.WarehouseId IN @HouseIds");
                }
                if (!string.IsNullOrWhiteSpace(input.GoodsCode))
                {
                    input.GoodsCode = $"%{input.GoodsCode}%";
                    conditions.Add("d.GoodsNo LIKE @GoodsCode");
                }
                if (!string.IsNullOrWhiteSpace(input.GoodsName))
                {
                    input.GoodsName = $"%{input.GoodsName}%";
                    conditions.Add("g.GoodsName LIKE @GoodsName");
                }
                if (!string.IsNullOrWhiteSpace(input.GoodsSpec))
                {
                    input.GoodsSpec = $"%{input.GoodsSpec}%";
                    conditions.Add("gs.SpecName LIKE @GoodsSpec");
                }



                if (!conditions.Any()) return null;

                string condition = $" AND {string.Join(" AND ", conditions)}";

                sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

                return conn.Connection.Query<int>(sql, input).ToList();
            }
        }
        /// <summary>
        /// 获取商品入库单明细
        /// </summary>
        /// <param name="entryId"></param>
        /// <returns></returns>
        public List<LKWarehouseEntryDetail> GetKWarehouseEntryDetails(int entryId)
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.GetListByWhere<LKWarehouseEntryDetail>("EntryId=@entryId", new { entryId }).ToList();
            }
        }

        //获取客户下的商品入库单明细列表
        public List<LKWarehouseEntryDetail> GetLKWarehouseEntryDetailsByCustomerId(int customerId)
        {
            using (var conn = new DbContext())
            {
                string sql = @"SELECT d.[Id]
      ,d.[EntryId]
      ,d.[GoodsNo]
      ,d.[GoodsId]
      ,d.[GoodsSpecId]
      ,d.[WareHouseId]
      ,d.[GoodsName]
      ,d.[GoodsSpecName]
      ,d.[WareHouseName]
      ,d.[Weight]
,d.Number AS OrginNumber
	  ,d.Number-COALESCE(od.OutNumber,0) AS Number
      ,d.[Area]
      ,d.[AvNumber]
	  ,d.TonWeight-COALESCE(od.TonWeight,0) AS TonWeight
      ,d.[PlateNumber]
      ,d.[ProductionDate]
      ,d.[ExpirationDate]
      ,d.[ProducingArea]
      ,d.[Remark]
      ,d.[Remark1]
      ,d.[Remark2]
      ,d.[Remark3]
      ,d.[Processed]
      ,d.[CreateTime]
      ,d.[IsDeleted],od.GoodsNo,od.OutNumber --,od.TonWeight
FROM dbo.LK_WarehouseEntryDetail d WITH(NOLOCK)
JOIN dbo.LK_WarehouseEntry e WITH(NOLOCK) ON d.EntryId=e.Id
LEFT JOIN (SELECT GoodsNo,SUM(OutNumber) AS OutNumber,SUM(TonWeight) AS TonWeight FROM dbo.LK_WarehouseOutDetail WITH(NOLOCK)
WHERE IsDeleted=0
GROUP BY GoodsNo) AS  od  ON d.GoodsNo=od.GoodsNo
WHERE d.IsDeleted=0 AND e.IsDeleted=0 AND (od.GoodsNo IS NULL OR d.Number>od.OutNumber) AND e.CustomerId=@customerId";
                return conn.Connection.Query<LKWarehouseEntryDetail>(sql, new { customerId }).ToList();
            }
        }

        /// <summary>
        /// 确认入库
        /// </summary>
        /// <param name="id"></param>
        /// <param name="contractNo"></param>
        /// <param name="contractType"></param>
        /// <returns></returns>
        public bool ConfirmLKWarehouseEntry(int id,string contractNo,int contractType)
        {
            return UseTransaction((conn, trans) =>
            {
                var result = conn.Execute("UPDATE LK_WarehouseEntry SET Status=1 WHERE Id=@id", new { id }, trans);

                //更新合同状态
                if (contractType == (int)BillType.ZeroWarehouseContract)
                {
                    conn.Execute("UPDATE LK_ZeroWarehouseContract SET Status=1 WHERE ContractNo=@contractNo", new { contractNo }, trans);
                }
                else 
                {
                    conn.Execute("UPDATE LK_WarehouseContract SET Status=1 WHERE ContractNo=@contractNo", new { contractNo }, trans);
                }


                return result > 0;
            });
        }
    }
}

